package com.apestech.framework.sql.lib;

import com.apestech.framework.db.SqlSession;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.sql.MultiDataBase;
import com.apestech.framework.sql.SqlUtil;
import com.apestech.framework.xml.table.modul.Tables;
import java.util.ArrayList;

/**
 * 针对单表创建、字段结构修改、SQL解析
 *
 * @author hup
 * @create 2018-03-21-9:05
 */
public class TableSql extends SqlUtil {
    private SqlSession session;
    private ArrayList<String> tableSql;
    private String primarySql;
    private ArrayList<String> commentSql=new ArrayList<String>();
    private ArrayList<String> foreignSql=new ArrayList<String>();
    private ArrayList<String> indexSql=new ArrayList<String>();
    private String sql="";
    private MultiDataBase db;
    private Tables table;

    public TableSql(SqlSession session){
        this.db=new MultiDataBase(session.getDbType());
        this.session=session;
    }

    public void setTable(Tables table) throws Exception {
        this.table = table;
    }

    public void getCreateSql() throws Exception {
        if(table==null) throw new Exception("表信息不允许为空，请检查配置及调用参数是否有误 ！");
        if(table.isSingleTable()){
            Tables.Table tab=table.getTable(table.getFirstTable());
            getTableSql(tab);
        } else {
            ArrayList<String> klist=table.getRelat().getOrderKeylist();
            int len=klist.size();
            for(int i=0; i<len; i++){
                getTableSql(table.getTable(klist.get(i)));
            }
        }
    }

    private void getTableSql(Tables.Table tab) throws Exception {
        sql="";
        primarySql="";
        commentSql.clear();
        foreignSql.clear();
        indexSql.clear();

        if(tab.getTabName()==null) throw new Exception("新建表表名不能为空！");
        sql="CREATE TABLE "+tab.getTabName()+" ( \n";
        if(tab.getTabComment()!=null){
            commentSql.add(db.getComment(tab.getTabComment(), tab.getTabName(), null));
        }
        int len=tab.getFields().size();
        for(int i=0; i<len; i++){
            Tables.Field f=tab.getFields().get(i);
            if(i>0) sql=sql+",\n";
            if(f.getComment()!=null){
                commentSql.add(db.getComment(f.getComment(), tab.getTabName(), f.getId()));
            }
//            if(f.getForeignKey()!=null && f.getForeignTab()!=null){
//                foreignSql.add(db.getForeignSql(tab.getTabName(), f.getId(), f.getForeignTab(), f.getForeignKey()));
//            }
            sql=sql+"    "+db.getCreateCol(f.getId(), f.getType(), f.isNotNull(), f.getDefaultValue());
            if(f.getForeignKey()!=null && f.getForeignTab()!=null){
                sql=sql+" REFERENCES "+f.getForeignTab()+"("+f.getForeignKey()+") ";
            }
        }
        if(tableSql==null) tableSql=new ArrayList<String>();
        if(!"".equals(tab.getPrimaryField())){
              primarySql=db.getPrimarySql(tab.getTabName(), tab.getPrimaryField());
        }
        ArrayList<String> klist=tab.getUniqueIndex().getKeylist();
        len=klist.size();
        for(int i=0; i<len; i++){
            String k=klist.get(i);
            indexSql.add(db.getUniqueIndexSql(tab.getTabName(), k, tab.getUniqueIndex().getString(k)));
        }
        tableSql.add(sql+"\n)");
        tableSql.add(primarySql);
        tableSql.addAll(commentSql);
        tableSql.addAll(foreignSql);
        tableSql.addAll(indexSql);
    }

    public ArrayList<String> getAddColumn(String tabname, JSONArray fields){
        tabname=tabname.toUpperCase();
        ArrayList<String> sql=new ArrayList<String>();
        commentSql.clear();
        foreignSql.clear();
        int len=fields.size();
        for(int i=0; i<len; i++){
            JSONObject f=fields.get(i);
            if(f.getString("comment")!=null){
                commentSql.add(db.getComment(f.getString("comment"), tabname, f.getString("id")));
            }
            if(f.getString("foreignKey")!=null && f.getString("foreignTab")!=null){
                foreignSql.add(db.getForeignSql(tabname, f.getString("id"), f.getString("foreignTab"), f.getString("foreignKey")));
            }
            String col="ALTER TABLE "+tabname+" ADD "+db.getCreateCol(f.getString("id"), f.getString("type"), "true".equals(f.getString("notNull")), f.getString("defaultValue"));
            sql.add(col);
        }
        sql.addAll(commentSql);
        sql.addAll(foreignSql);
        return sql;
    }

    public ArrayList<String> getSql(){
        return tableSql;
    }

    public String dropTable(String tabName) throws Exception {
        return db.getDropTable(tabName);
    }

    public String getDropSequence(String seqName) throws Exception {
        return db.getDropSequence(seqName);
    }

    public String deleteTable(String tabName) throws Exception {
        return db.getDeleteTable(tabName);
    }

    public ArrayList<String> dropTable(SqlSession session) throws Exception {
        if(table==null) throw new Exception("表信息不允许为空，请检查配置及调用参数是否有误 ！");
        ArrayList<String> sql=new ArrayList<String>();
        if(table.isSingleTable()){
            Tables.Table tab=table.getTable(table.getFirstTable());
            String tabname=tab.getTabName();
            if(session.tableExists(tabname)) sql.add(dropTable(tabname));
            String seqName="E_SEQ"+tabname;
            if(session.sequenceExists(seqName)){
                sql.add(getDropSequence(seqName));
                sql.add("DELETE EXE_ID WHERE TABLE_ID='"+tabname+"' ");
            }
        } else {
            ArrayList<String> klist=table.getRelat().getOrderKeylist();
            int len=klist.size();
            for(int i=len-1; i>=0; i--){
                String tabname=table.getTable(klist.get(i)).getTabName();
                if(session.tableExists(tabname)) sql.add(dropTable(tabname));
                String seqName="E_SEQ"+tabname;
                if(session.sequenceExists(seqName)){
                    sql.add(getDropSequence(seqName));
                    sql.add("DELETE EXE_ID WHERE TABLE_ID='"+tabname+"' ");
                }
            }
        }
        return sql;
    }

    public ArrayList<String> deleteTable() throws Exception {
        if(table==null) throw new Exception("表信息不允许为空，请检查配置及调用参数是否有误 ！");
        ArrayList<String> sql=new ArrayList<String>();
        if(table.isSingleTable()){
            Tables.Table tab=table.getTable(table.getFirstTable());
            String tabname=tab.getTabName();
            if(tabname==null) tabname=((Tables.TableRelat)table.getRelat().get(table.getRelat().getFirstKey())).getName();
            sql.add(deleteTable(tabname));
        } else {
            ArrayList<String> klist=table.getRelat().getOrderKeylist();
            int len=klist.size();
            for(int i=len-1; i>=0; i--){
                String tabname=table.getTable(klist.get(i)).getTabName();
                if(tabname==null) tabname=((Tables.TableRelat)table.getRelat().get(klist.get(i))).getName();
                sql.add(deleteTable(tabname));
            }
        }
        return sql;
    }

    public Tables getTable() {
        return table;
    }

    public ArrayList<String> getAlterAddColumn(String tabname, String field, String type, String comment) throws Exception {
        ArrayList<String> list=new ArrayList<String>();
        list.add("ALTER TABLE "+tabname+" ADD "+field+" "+type);
        if(comment!=null && !"".equals(comment.trim())){
            list.add(db.getComment(comment.trim(), tabname, field));
        }
        return list;
    }

    public ArrayList<String> getAlterColumnType(String tabname, String field, String type) throws Exception {
        tabname=tabname.toUpperCase();
        Table tab=session.getDictTable(tabname);
        ArrayList<String> sql=new ArrayList<String>();
        String tempField=field+"_NEW";
        Type types=getColumnType(type.toUpperCase());
        String ftype=tab.getTypeName(field);
        Integer flength=tab.getLength(field);
        Integer fSize=tab.getSize(field);
        String ufield=tempField;
        Type type1=getColumnType(type);
        if(type1.getType().equals(ftype)){
            boolean flag=type1.getLength()!=null && flength<=type1.getLength();
            flag=flag||type1.getSize()!=null && fSize<=type1.getSize();
            if(flag){
                sql.add("ALTER TABLE "+tabname+" MODIFY "+field+" "+type);
                return sql;
            }
        }

        JSONArray list=session.getForJSON().query("SELECT MAX("+field+") AS "+field+" FROM "+tabname+" WHERE 1=1 ");
        int len=0;
        String value=null;
        if(list!=null){
            len=list.size();
            value=list.get(0).getString(field);
            if(value==null) len=0;
        }
        if(len>0){
            int size=value.length();
            if(ftype.indexOf("NUMBER")>=0){  //原字段类型
                if(types.getType().indexOf("DATE")>=0) {
                    if (fSize!=null && fSize>0) throw new Exception("double类型不允许转成[DATE]类型！");
                    if (flength<14) throw new Exception("只有大于等于14位的长整型才允许转成[DATE]类型！");
                    if(size==17) ufield=db.getTimeStampToNumberToDate(tempField);
                    else ufield=db.getNumberToDate(tempField);
                } else if(types.getType().indexOf("TIME")>=0){
                    if(fSize!=null && fSize>0) throw new Exception("double类型不允许转成[TIMESTAMP]类型！");
                    if(flength<14) throw new Exception("只有大于等于14位的长整型才允许转成[TIMESTAMP]类型！");
                    if(size==14) ufield=db.getDateToNumberToTimeStamp(tempField);
                    else ufield=db.getNumberToTimeStamp(tempField);
                } else if(types.getType().indexOf("CHAR")>=0 && size>types.getLength()) throw new Exception("当前数据长度["+size+"]已经超过值已超过设置长度["+types.getLength()+"]！");
            } else if(ftype.indexOf("DATE")>=0){
                if(types.getType().indexOf("NUMBER")>=0) {
                    if (types.getSize() != null) throw new Exception("时间类型不允许转成浮点型！");
                    else if (types.getLength() < 14) throw new Exception("新设置长度必须大于等于14！");
                    ufield=db.getDateToNumber(tempField);
                } else if(types.getType().indexOf("TIME")>=0) {
                    ufield=db.getDateToTimeStamp(tempField);
                } else if(types.getType().indexOf("CHAR")>=0){
                    if(types.getLength()<19) throw new Exception("新设置长度必须大于等于19！");
                    ufield=db.getDateToStr(tempField);
                }
            } else if(ftype.indexOf("TIME")>=0){
                if(types.getType().indexOf("NUMBER")>=0) {
                    if (types.getSize() != null) throw new Exception("时间类型不允许转成浮点型！");
                    else if (types.getLength() < 17) throw new Exception("新设置长度必须大于等于17！");
                    ufield = db.getTimeStampToNumber(tempField);
                } else if(types.getType().indexOf("DATE")>=0){
                    ufield=db.getTimeStampToDate(tempField);
                } else if(types.getType().indexOf("CHAR")>=0){
                    if(types.getLength()<23) throw new Exception("新设置长度必须大于等于23！");
                    ufield=db.getTimeStampToStr(tempField);
                }
            } else {
                if(types.getType().indexOf("DATE")>=0) {
                    if (flength<19) throw new Exception("只有大于等于19位字串才允许转成[DATE]类型！");
                    ufield=db.getTimeStampToStrToDate(tempField);
                } else if(types.getType().indexOf("TIME")>=0){
                    if (flength<19) throw new Exception("只有大于等于19位字串才允许转成[TIMESTAMP]类型！");
                    if(size<19) throw new Exception("当前数据库的值必须大于等于19位字串才允许转成[TIMESTAMP]类型！");
                    ufield=db.getStrToTimeStamp(tempField);
                } else if(types.getType().indexOf("NUMBER")>=0){
                    if(size>types.getLength()) throw new Exception("当前数据长度["+size+"]已经超过值已超过设置长度["+types.getLength()+"]！");
                    if(!isNumber(value)) throw new Exception("当前值不是数值，不允许转数值！");
                }
            }
        }
        boolean pkey=false;
        boolean notnull=false;
        if((","+tab.getPkKey().getPkColumn()+",").indexOf(","+field+",")>=0) pkey=true;
        if("1".equals(tab.getNotNull(field))) notnull=true;
        if(len>0) sql.add("ALTER TABLE "+tabname+" ADD "+tempField+" "+tab.getDbType(field));
        if(pkey) sql.add("ALTER TABLE "+tabname+" DROP CONSTRAINT "+tab.getPkKey().getPkName()+" CASCADE");
        if(len>0) sql.add("UPDATE "+tabname+" SET "+tempField+"="+field);
        if(notnull && len>0) sql.add("ALTER TABLE "+tabname+" MODIFY "+field+" NULL");
        if(len>0) sql.add("UPDATE "+tabname+" SET "+field+"=NULL");
        sql.add("ALTER TABLE "+tabname+" DROP COLUMN "+field);
        sql.add("ALTER TABLE "+tabname+" ADD "+field+" "+type);
        if(len>0) sql.add("UPDATE "+tabname+" SET "+field+"="+ufield);
        if(len>0) sql.add("ALTER TABLE "+tabname+" DROP COLUMN "+tempField);
        if(notnull && len>0) sql.add("ALTER TABLE "+tabname+" MODIFY "+field+" NOT NULL");
        if(pkey) sql.add("ALTER TABLE "+tabname+" ADD CONSTRAINT "+tab.getPkKey().getPkName()+" PRIMARY KEY("+tab.getPkKey().getPkColumn()+")");
        return sql;
    }

    /*create table tb_dept(
        Id int primary key auto_increment,#部门编号 整形 主键 自增长
        Name varchar(18),#部门名称
        description varchar(100)#描述
    );*/

}
